﻿// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using System.Linq;

namespace SnapObjects.Data.SqlServer
{
    internal abstract class SqlServerRoutineParameterSchemaProvider : RoutineParameterSchemaProvider
    {
        public SqlServerRoutineParameterSchemaProvider(DataContext dataContext) : base(dataContext)
        {
        }

        protected abstract string GetSchemaQuerySql(string database, string condition);
        protected abstract string IsRoutineDefinedInCurrentSchemaSql(string database, string procedureName);

        protected override IList<DynamicModel> QueryParameters(RoutineNameInfo nameInfo, ISqlGenerationHelper helper)
        {
            var result = this.InternalQueryParameters(nameInfo, helper);

            if (result.Count > 0)
            {
                return result;
            }

            return this.QueryParametersFromSynonyms(nameInfo, helper);
        }

        protected async override Task<IList<DynamicModel>> QueryParametersAsync(
            RoutineNameInfo nameInfo, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var result = await this.InternalQueryParametersAsync(nameInfo, helper, cancellationToken);

            if (result.Count > 0)
            {
                return result;
            }

            return await this.QueryParametersFromSynonymsAsync(nameInfo, helper, cancellationToken);
        }

        private IList<DynamicModel> QueryParametersFromSynonyms(RoutineNameInfo nameInfo, ISqlGenerationHelper helper)
        {
            if (_dataContext.SqlExecutor is SqlExecutor sqlExecutor)
            {
                var sqlText = this.GetSynonymsSql(nameInfo);

                var result = sqlExecutor.Select<DynamicModel>(sqlText, false);

                if (result.Count == 1)
                {
                    var refObjectName = result[0].GetValue<string>(0);

                    if (!String.IsNullOrWhiteSpace(refObjectName))
                    {
                        var tmpNameInfo = this.ParseName(refObjectName);

                        return this.InternalQueryParameters(tmpNameInfo, helper);
                    }
                }
            }

            return new List<DynamicModel>();
        }

        private async Task<IList<DynamicModel>> QueryParametersFromSynonymsAsync(
            RoutineNameInfo nameInfo, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            if (_dataContext.SqlExecutor is SqlExecutor sqlExecutor)
            {
                var sqlText = this.GetSynonymsSql(nameInfo);

                var result = await sqlExecutor.SelectAsync<DynamicModel>(sqlText, false, new object[] { }, cancellationToken);

                if (result.Count == 1)
                {
                    var refObjectName = result[0].GetValue<string>(0);

                    if (!String.IsNullOrWhiteSpace(refObjectName))
                    {
                        var tmpNameInfo = this.ParseName(refObjectName);

                        return await this.InternalQueryParametersAsync(tmpNameInfo, helper, cancellationToken);
                    }
                }
            }

            return new List<DynamicModel>();
        }

        private IList<DynamicModel> InternalQueryParameters(RoutineNameInfo nameInfo, ISqlGenerationHelper helper)
        {
            var condition = this.GetCondition(nameInfo, helper);

            var sql = this.GetSchemaQuerySql(nameInfo.Database, condition);

            return _dataContext.SqlExecutor.Select<DynamicModel>(sql);
        }

        private async Task<IList<DynamicModel>> InternalQueryParametersAsync(
            RoutineNameInfo nameInfo, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var condition = await this.GetConditionAsync(nameInfo, helper, cancellationToken);

            var sql = this.GetSchemaQuerySql(nameInfo.Database, condition);

            return await _dataContext.SqlExecutor.SelectAsync<DynamicModel>(sql, cancellationToken);
        }

        private string GetCondition(RoutineNameInfo nameInfo, ISqlGenerationHelper helper)
        {
            var name = helper.RemoveDelimitIdentifier(nameInfo.Name);
            var schema = helper.RemoveDelimitIdentifier(nameInfo.Schema);

            var condition = $" obj.name='{name}' ";

            if (!String.IsNullOrWhiteSpace(schema))
            {
                condition += $" AND sm.name='{schema}' ";
            }
            else
            {
                var checkProcedureSql = this.IsRoutineDefinedInCurrentSchemaSql(nameInfo.Database, name);

                var ret = _dataContext.SqlExecutor.Scalar<string>(checkProcedureSql);

                if (String.IsNullOrWhiteSpace(ret))
                {
                    condition += " AND obj.object_id < 0 ";
                }
                else
                {
                    condition += " AND obj.schema_id=SCHEMA_ID() ";
                }
            }

            return condition;
        }

        private async Task<string> GetConditionAsync(
            RoutineNameInfo nameInfo, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var name = helper.RemoveDelimitIdentifier(nameInfo.Name);
            var schema = helper.RemoveDelimitIdentifier(nameInfo.Schema);

            var condition = $" obj.name='{name}' ";

            if (!String.IsNullOrWhiteSpace(schema))
            {
                condition += $" AND sm.name='{schema}' ";
            }
            else
            {
                var checkProcedureSql = this.IsRoutineDefinedInCurrentSchemaSql(nameInfo.Database, name);

                var ret = await _dataContext.SqlExecutor
                    .ScalarAsync<string>(checkProcedureSql, cancellationToken);

                if (String.IsNullOrWhiteSpace(ret))
                {
                    condition += " AND obj.object_id < 0 ";
                }
                else
                {
                    condition += " AND obj.schema_id=SCHEMA_ID() ";
                }
            }

            return condition;
        }

        private string GetSynonymsSql(RoutineNameInfo nameInfo)
        {
            var schema = String.IsNullOrWhiteSpace(nameInfo.Schema) ? String.Empty : $"'{nameInfo.Schema}'";

            var sqlText = $"select base_object_name from sys.synonyms where schema_id = SCHEMA_ID({schema}) and name='{nameInfo.Name}'";

            return sqlText;
        }
    }
}
